﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System.Data.SqlClient;
using System.Data;

namespace Yubis.Data.SqlServer
{
    public class FieldMultiLevel : IFieldMultiLevel
    {
        string _name;
        string _tableName;

        public FieldMultiLevel(string name)
        {
            _name = name;
            _tableName = "Field_MultiLevel$" + name;
        }

        public void Create()
        {
            ServerConnection connection = new ServerConnection();
            connection.ConnectionString = Yubis.Data.Properties.Settings.Default.ConnectionString;

            Server server = new Server(connection);
            server.ConnectionContext.Connect();

            Database database = server.Databases[server.ConnectionContext.SqlConnectionObject.Database];

            Table table = new Table(database, _tableName);

            table.Columns.Add(new Column(table, "Id", DataType.UniqueIdentifier));
            table.Columns.Add(new Column(table, "MasterId", DataType.UniqueIdentifier));
            table.Indexes.Add(new Index(table, "PK_" + _tableName));
            table.Indexes["PK_" + _tableName].IndexKeyType = IndexKeyType.DriPrimaryKey;
            table.Indexes["PK_" + _tableName].IndexedColumns.Add(new IndexedColumn(table.Indexes["PK_" + _tableName], "Id"));
            table.Create();

            SqlCommand command = new SqlCommand();
            command.Connection = server.ConnectionContext.SqlConnectionObject;
            command.CommandText = "INSERT INTO Field_MultiLevel ( Id, Name ) VALUES ( @Id, @Name )";

            command.Parameters.Add("@Id", SqlDbType.UniqueIdentifier);
            command.Parameters.Add("@Name", SqlDbType.NVarChar);

            command.Parameters["@Id"].Value = Guid.NewGuid();
            command.Parameters["@Name"].Value = _name;
            command.ExecuteNonQuery();

        }

    }
}
